import os,sys
import re
import time
import pymysql
import wx   #wxpython
import wx.xrc
import wx.grid as grid
import pathlib
from myGUI import *
from openpyxl import Workbook #openpyxl

root=pathlib.Path(os.path.dirname(os.path.realpath(sys.argv[0])))
conn=pymysql.connect(host='192.168.2.200',user='root',passwd='root',db='kwdata',charset='utf8')
cur=conn.cursor()
wb = Workbook()
ws=wb.active

class MyDataTable(grid.PyGridTableBase):
    def __init__(self, data, colLabels=None):
        grid.GridTableBase.__init__(self)
        self.colLabels = colLabels
        self.data = data

    def GetNumberRows(self):
        return len(self.data)

    def GetNumberCols(self):
        if (self.colLabels != None) :
            l= len(self.colLabels)
        else:
            l= len(self.data[0])
        return l

    def GetValue(self, row, col):
        if self.data[row][col] is None:
            return ' '
        else:
            return self.data[row][col]

    def SetValue(self, row, col, value):
        pass

    def GetColLabelValue(self, col):
        if self.colLabels != None:
            return self.colLabels[col]
        else:
            return "ABCDEFGHIJKLMNOPQRSTUVWXYZ"[col]


class MP1(MyPanel1):
    def __init__(self, parent):
        super().__init__(parent)
        self.order_by2="DESC"
        self.order_by1="`日期` "
        self.order_by3=0
        self.group_by="tresult.pfdate"
        self.group=" "
        self.tableTitle=[u"班级",u"考号", u"姓名", u"选择题", u"判断题", u"操作题", u"总分", u"日期", u"Word", u"Excel", u"PPT", u"操作题详情"]
        
        self.sqlb="SELECT tstu.Class AS `班级`,tstu.stuNum AS `考号`, tstu.`Name` AS `姓名`,  d.`选择题`,  d.`判断题`,  d.`操作题`,  d.`总分`,  d.`日期`,  d.`操作题详情` FROM  tstu  RIGHT JOIN ( SELECT substring( sysset.setvalue, 5 ) AS c FROM sysset WHERE sysset.`name` = 'CurGradeClass' ) AS c ON tstu.Class = c.c  LEFT JOIN ( SELECT  * FROM  ( SELECT  tresult.choicescore AS `选择题`,  tresult.judgescore AS `判断题`,  tresult.operationscore AS `操作题`,  tresult.operationdesc AS `操作题详情`,  tresult.totalscore AS `总分`,  tresult.pfdate AS `日期`,  tresult.studentid AS sid FROM  tresult WHERE  tresult.pfdate >= date_format( now( ), '%y-%m-%d' ) ORDER BY  tresult.studentid, {:s} DESC ) b {:s} ) d ON d.sid = tstu.stuId ORDER BY "

        self.showpage()
    def __del__(self):
        pass
    def getSql(self):
        if(self.order_by3 in (3,4,5,6)):
            sqlt=self.sqlb.format(self.group_by,self.group)+' CAST('+ self.order_by1+'AS DECIMAL) '+self.order_by2
        else:
            sqlt=self.sqlb.format(self.group_by,self.group)+self.order_by1+self.order_by2
        return sqlt
    def showpage(self):
        sqlt=self.getSql()
        cur.execute(sqlt)
        conn.commit()
        t=cur.fetchall()
        if len(t)!=0:
            self.data=[]
            for i in t:
                (w,p,e)=self.getmoreinfo(i[8])
                self.data.append((i[0],i[1],i[2],i[3],i[4],i[5],i[6],i[7],w,e,p,i[8]))
                w=None
                e=None
                p=None
        self.infoTable = MyDataTable(self.data, self.tableTitle)
        self.m_grid1.SetTable(self.infoTable, True) 
        self.m_grid1.SetColSize(0, 50)
        self.m_grid1.SetColSize(1, 120)  
        self.m_grid1.SetColSize(2, 50) 
        self.m_grid1.SetColSize(3, 50) 
        self.m_grid1.SetColSize(4, 50) 
        self.m_grid1.SetColSize(5, 50) 
        self.m_grid1.SetColSize(5, 50)
        self.m_grid1.SetColSize(7, 130) 
        self.m_grid1.SetColSize(8, 40) 
        self.m_grid1.SetColSize(9, 40) 
        self.m_grid1.SetColSize(10, 40) 
        self.m_grid1.Refresh()
    def Onchoiced(self,event):
        print(event.String)
    def OnExit( self, event ):
        sys.exit(0)
        event.Skip()
    def m_grid1OnGridCellRightClick(self,event):
        event.Skip()
    def te(self,event):
        lt=self.m_grid1.GetSelectionBlockTopLeft()
        rb=self.m_grid1.GetSelectionBlockBottomRight()
        if(len(lt)>0):
            print(rb[0][0]-lt[0][0]+1,rb[0][1]-lt[0][1]+1)
            print(lt[0],rb[0])
            w.m_statusBar1.SetStatusText(f"选中{rb[0][0]-lt[0][0]+1}行{rb[0][1]-lt[0][1]+1}列")
        else:
            w.m_statusBar1.SetStatusText(u"")
        event.Skip()
    def m_button2OnButtonClick(self,event):
        print("导出")
        ws.column_dimensions['B'].width = 16
        ws.column_dimensions['H'].width = 20
        for i in self.tableTitle:
            n=i
            n=n.replace("▼","")
            n=n.replace("▲","")
            ws.cell(1,self.tableTitle.index(i)+1,n)

        sqlt=self.getSql()
        cur.execute(sqlt)
        t=cur.fetchall()
        if len(t)!=0:
            r=1
            for i in t:
                (w,p,e)=self.getmoreinfo(i[8])
                i1=i[:8]+(w,e,p,i[8])
                l=0
                r=r+1
                ws.cell(r,12," ")
                for n in i1 :
                    l=l+1
                    if(i1.index(n) in (3,4,5,6,8,9) and n!="" and n!= None):
                        ws.cell(r,l,float(n))
                    else:
                        ws.cell(r,l,n)
                
        t=4
        while(t):
            try:
                wb.save(root/f'{self.data[0][0]}班{(time.strftime("%Y-%m-%d_%H.%M", time.localtime()))}.xlsx')
            except:
                msg(f'{self.data[0][0]}班{(time.strftime("%Y-%m-%d_%H.%M", time.localtime()))}.xlsx')
            else:
                msg("导出成功")
                break
            finally:
                t=t-1
        event.Skip()
    def m_grid1OnGridLabelLeftClick( self, event ):
        print(event.Col)
        if(event.Col<=6):
            if(self.order_by3==event.Col):
                if(self.order_by2=="DESC"):
                    self.order_by2="ASC"
                    self.tableTitle[self.order_by3]=self.tableTitle[self.order_by3][:-1]+"▲"
                else:
                    self.order_by2="DESC" 
                    self.tableTitle[self.order_by3]=self.tableTitle[self.order_by3][:-1]+"▼"   
                pass
            else:
                self.tableTitle[self.order_by3]=(self.tableTitle[self.order_by3]).replace("▼","")
                self.tableTitle[self.order_by3]=(self.tableTitle[self.order_by3]).replace("▲","")
                self.order_by3=event.Col
                self.tableTitle[self.order_by3]=self.tableTitle[self.order_by3]+"▼"
                self.order_by1=self.tableTitle[self.order_by3][:-1]+" "
                self.order_by2="DESC"
        self.showpage()

        event.Skip()
    def m_grid1OnGridCellLeftDClick( self, event ):
        print(self.data[event.Row][11])
        print(event.Col,event.Row)
        msg(self.data[event.Row][11],"操作题详情",wx.OK)
        event.Skip()
    def OnRefresh(self,event):
        self.showpage()
        event.Skip()
    def MaxTotalscore(self,event):
        self.group_by="CAST( tresult.totalscore AS DECIMAL)"
        self.group=" GROUP BY b.sid "
        print(self.sqlb.format(self.group_by,self.group))
        self.showpage()
        event.Skip()
    def MaxOperationscore(self,event):
        self.group_by="CAST( tresult.operationscore AS DECIMAL)"
        self.group=" GROUP BY b.sid "
        print(self.sqlb.format(self.group_by,self.group))
        self.showpage()
        event.Skip()
    def LastTime(self,event):
        self.group_by="tresult.pfdate"
        self.group=" GROUP BY b.sid "
        print(self.sqlb.format(self.group_by,self.group))
        self.showpage()
        event.Skip()
    def AllList(self,event):
        self.group_by="tresult.pfdate"
        self.group=" "
        print(self.sqlb.format(self.group_by,self.group))
        self.showpage()
        event.Skip()
    def getmoreinfo(self,date):
        try:
            matchObj = re.search( r'Word 2010\(得分为:(\d+\.\d+)', date)
            w=matchObj.group()[14:]    
        except:
            w=""   
        try:   
            matchObj = re.search( r'Excel 2010\(得分为:(\d+\.\d+)', date)
            e=matchObj.group()[15:] 
        except:
            try:   
                matchObj = re.search( r'Access 2010\(得分为:(\d+\.\d+)', date)
                e=matchObj.group()[16:] 
            except:                    
                e="" 
        try:              
            matchObj = re.search( r'PowerPoint 2010\(得分为:(\d+\.\d+)', date)
            p=matchObj.group()[20:]
        except:
            p=""
        return (w,p,e)
def msg(content, title="", type=wx.ICON_INFORMATION):
    dlg = wx.MessageDialog(None, content, title, type)
    r = dlg.ShowModal()
    dlg.Destroy()
    return r


'''
wx.OK                     Show an OK button.
wx.CANCEL                 Show a Cancel button.
wx.YES_NO                 Show Yes and No buttons.
wx.YES_DEFAULT          Used with wxYES_NO, makes Yes button the default - which is the default behaviour.
wx.NO_DEFAULT             Used with wxYES_NO, makes No button the default.
wx.ICON_EXCLAMATION     Shows an exclamation mark icon.
wx.ICON_HAND             Shows an error icon.
wx.ICON_ERROR             Shows an error icon - the same as  wx.ICON_HAND.
wx.ICON_QUESTION         Shows a question mark icon.
wx.ICON_INFORMATION     Shows an information (i) icon.
wx.STAY_ON_TOP          The message box stays on top of all other window, even those of the other applications (Windows only). 

'''
app = wx.App()
w = MyFrame1(None)
p = MP1(w)
w.Show()
app.MainLoop()
